{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import os\n",
    "import matplotlib.font_manager as fm\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib\n",
    "import math\n",
    "import random\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 读取预处理过的数据\n",
    "original = pd.read_excel('./第二问预处理.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 选择 2023 年 6 月 24-30 日的数据\n",
    "original['销售日期'] = pd.to_datetime(original['销售日期'])\n",
    "recent_data = original[(original['销售日期'] >= '2023-06-24') & (original['销售日期'] <= '2023-06-30')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>销售日期</th>\n",
       "      <th>扫码销售时间</th>\n",
       "      <th>单品编码</th>\n",
       "      <th>辅助列</th>\n",
       "      <th>销量(千克)</th>\n",
       "      <th>销售单价(元/千克)</th>\n",
       "      <th>销售类型</th>\n",
       "      <th>是否打折销售</th>\n",
       "      <th>分类编码</th>\n",
       "      <th>分类名称</th>\n",
       "      <th>批发价格(元/千克)</th>\n",
       "      <th>损耗率(%)</th>\n",
       "      <th>成本加成定价(元/千克)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>875001</th>\n",
       "      <td>2023-06-24</td>\n",
       "      <td>09:10:47.276</td>\n",
       "      <td>102900011032022</td>\n",
       "      <td>45101-102900011032022</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2.8</td>\n",
       "      <td>销售</td>\n",
       "      <td>否</td>\n",
       "      <td>1011010504</td>\n",
       "      <td>辣椒类</td>\n",
       "      <td>1.51</td>\n",
       "      <td>9.43</td>\n",
       "      <td>2.957219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>875002</th>\n",
       "      <td>2023-06-24</td>\n",
       "      <td>09:10:47.531</td>\n",
       "      <td>102900005115786</td>\n",
       "      <td>45101-102900005115786</td>\n",
       "      <td>0.379</td>\n",
       "      <td>4.0</td>\n",
       "      <td>销售</td>\n",
       "      <td>否</td>\n",
       "      <td>1011010101</td>\n",
       "      <td>花叶类</td>\n",
       "      <td>2.41</td>\n",
       "      <td>13.62</td>\n",
       "      <td>4.379998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>875003</th>\n",
       "      <td>2023-06-24</td>\n",
       "      <td>09:13:51.795</td>\n",
       "      <td>102900005115762</td>\n",
       "      <td>45101-102900005115762</td>\n",
       "      <td>0.505</td>\n",
       "      <td>4.0</td>\n",
       "      <td>销售</td>\n",
       "      <td>否</td>\n",
       "      <td>1011010101</td>\n",
       "      <td>花叶类</td>\n",
       "      <td>2.31</td>\n",
       "      <td>18.52</td>\n",
       "      <td>4.525052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>875004</th>\n",
       "      <td>2023-06-24</td>\n",
       "      <td>09:14:43.77</td>\n",
       "      <td>102900005115762</td>\n",
       "      <td>45101-102900005115762</td>\n",
       "      <td>0.505</td>\n",
       "      <td>4.0</td>\n",
       "      <td>销售</td>\n",
       "      <td>否</td>\n",
       "      <td>1011010101</td>\n",
       "      <td>花叶类</td>\n",
       "      <td>2.31</td>\n",
       "      <td>18.52</td>\n",
       "      <td>4.525052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>875005</th>\n",
       "      <td>2023-06-24</td>\n",
       "      <td>09:14:44.044</td>\n",
       "      <td>102900011030059</td>\n",
       "      <td>45101-102900011030059</td>\n",
       "      <td>1.000</td>\n",
       "      <td>4.5</td>\n",
       "      <td>销售</td>\n",
       "      <td>否</td>\n",
       "      <td>1011010101</td>\n",
       "      <td>花叶类</td>\n",
       "      <td>3.32</td>\n",
       "      <td>9.43</td>\n",
       "      <td>4.845673</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>878498</th>\n",
       "      <td>2023-06-30</td>\n",
       "      <td>21:35:13.264</td>\n",
       "      <td>102900005115250</td>\n",
       "      <td>45107-102900005115250</td>\n",
       "      <td>0.284</td>\n",
       "      <td>24.0</td>\n",
       "      <td>销售</td>\n",
       "      <td>否</td>\n",
       "      <td>1011010801</td>\n",
       "      <td>食用菌</td>\n",
       "      <td>15.60</td>\n",
       "      <td>10.80</td>\n",
       "      <td>25.888789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>878499</th>\n",
       "      <td>2023-06-30</td>\n",
       "      <td>21:35:14.358</td>\n",
       "      <td>102900011022764</td>\n",
       "      <td>45107-102900011022764</td>\n",
       "      <td>0.669</td>\n",
       "      <td>12.0</td>\n",
       "      <td>销售</td>\n",
       "      <td>否</td>\n",
       "      <td>1011010501</td>\n",
       "      <td>茄类</td>\n",
       "      <td>7.00</td>\n",
       "      <td>6.90</td>\n",
       "      <td>12.518797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>878500</th>\n",
       "      <td>2023-06-30</td>\n",
       "      <td>21:35:20.264</td>\n",
       "      <td>102900005115250</td>\n",
       "      <td>45107-102900005115250</td>\n",
       "      <td>0.125</td>\n",
       "      <td>24.0</td>\n",
       "      <td>销售</td>\n",
       "      <td>否</td>\n",
       "      <td>1011010801</td>\n",
       "      <td>食用菌</td>\n",
       "      <td>15.60</td>\n",
       "      <td>10.80</td>\n",
       "      <td>25.888789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>878501</th>\n",
       "      <td>2023-06-30</td>\n",
       "      <td>21:35:21.509</td>\n",
       "      <td>102900011016701</td>\n",
       "      <td>45107-102900011016701</td>\n",
       "      <td>0.252</td>\n",
       "      <td>5.2</td>\n",
       "      <td>销售</td>\n",
       "      <td>否</td>\n",
       "      <td>1011010504</td>\n",
       "      <td>辣椒类</td>\n",
       "      <td>3.63</td>\n",
       "      <td>5.70</td>\n",
       "      <td>5.419417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>878502</th>\n",
       "      <td>2023-06-30</td>\n",
       "      <td>21:40:48.248</td>\n",
       "      <td>102900011022764</td>\n",
       "      <td>45107-102900011022764</td>\n",
       "      <td>0.803</td>\n",
       "      <td>12.0</td>\n",
       "      <td>销售</td>\n",
       "      <td>否</td>\n",
       "      <td>1011010501</td>\n",
       "      <td>茄类</td>\n",
       "      <td>7.00</td>\n",
       "      <td>6.90</td>\n",
       "      <td>12.518797</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3502 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             销售日期        扫码销售时间             单品编码                    辅助列  \\\n",
       "875001 2023-06-24  09:10:47.276  102900011032022  45101-102900011032022   \n",
       "875002 2023-06-24  09:10:47.531  102900005115786  45101-102900005115786   \n",
       "875003 2023-06-24  09:13:51.795  102900005115762  45101-102900005115762   \n",
       "875004 2023-06-24   09:14:43.77  102900005115762  45101-102900005115762   \n",
       "875005 2023-06-24  09:14:44.044  102900011030059  45101-102900011030059   \n",
       "...           ...           ...              ...                    ...   \n",
       "878498 2023-06-30  21:35:13.264  102900005115250  45107-102900005115250   \n",
       "878499 2023-06-30  21:35:14.358  102900011022764  45107-102900011022764   \n",
       "878500 2023-06-30  21:35:20.264  102900005115250  45107-102900005115250   \n",
       "878501 2023-06-30  21:35:21.509  102900011016701  45107-102900011016701   \n",
       "878502 2023-06-30  21:40:48.248  102900011022764  45107-102900011022764   \n",
       "\n",
       "        销量(千克)  销售单价(元/千克) 销售类型 是否打折销售        分类编码 分类名称  批发价格(元/千克)  损耗率(%)  \\\n",
       "875001   1.000         2.8   销售      否  1011010504  辣椒类        1.51    9.43   \n",
       "875002   0.379         4.0   销售      否  1011010101  花叶类        2.41   13.62   \n",
       "875003   0.505         4.0   销售      否  1011010101  花叶类        2.31   18.52   \n",
       "875004   0.505         4.0   销售      否  1011010101  花叶类        2.31   18.52   \n",
       "875005   1.000         4.5   销售      否  1011010101  花叶类        3.32    9.43   \n",
       "...        ...         ...  ...    ...         ...  ...         ...     ...   \n",
       "878498   0.284        24.0   销售      否  1011010801  食用菌       15.60   10.80   \n",
       "878499   0.669        12.0   销售      否  1011010501   茄类        7.00    6.90   \n",
       "878500   0.125        24.0   销售      否  1011010801  食用菌       15.60   10.80   \n",
       "878501   0.252         5.2   销售      否  1011010504  辣椒类        3.63    5.70   \n",
       "878502   0.803        12.0   销售      否  1011010501   茄类        7.00    6.90   \n",
       "\n",
       "        成本加成定价(元/千克)  \n",
       "875001      2.957219  \n",
       "875002      4.379998  \n",
       "875003      4.525052  \n",
       "875004      4.525052  \n",
       "875005      4.845673  \n",
       "...              ...  \n",
       "878498     25.888789  \n",
       "878499     12.518797  \n",
       "878500     25.888789  \n",
       "878501      5.419417  \n",
       "878502     12.518797  \n",
       "\n",
       "[3502 rows x 13 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "recent_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[102900011032022 102900005115786 102900005115762 102900011030059\n",
      " 102900005115823 102900011006948 102900005115250 102900051000463\n",
      " 102900011016701 102900011030097 102900011035078 106971533450003\n",
      " 102900005116714 102900005116899 102900011031100 102900005119975\n",
      " 102900005116257 102900011034026 102900011008164 102900005115908\n",
      " 102900005118831 102900011031926 102900011022764 106949711300259\n",
      " 102900011023464 102900005115946 102900011034330 102900005118824\n",
      " 102900051000944 102900005116509 102900011032251 102900011000328\n",
      " 102900005118817 102900011035740 102900011001691 102900011032848\n",
      " 102900011032343 102900011030110 102900011007969 102900011034439\n",
      " 102900011032237 102900005115984 102900011032732 102900005115779\n",
      " 102900011013274 102900011030929 102900011018132 102900011033982\n",
      " 102900011036686]\n",
      "49\n"
     ]
    }
   ],
   "source": [
    "# 去除重复值并获取去重后的单品编码列表\n",
    "unique_codes = recent_data['单品编码'].drop_duplicates().unique()\n",
    "\n",
    "# 计算去重后的单品编码个数\n",
    "num_unique_codes = len(unique_codes)\n",
    "\n",
    "print(unique_codes)\n",
    "print(num_unique_codes)\n",
    "\n",
    "# 创建一个DataFrame对象\n",
    "df = pd.DataFrame(unique_codes, columns=['单品编码'])\n",
    "\n",
    "# 导出为Excel文件\n",
    "df.to_excel('./results/49个符合的单品.xlsx', index=False)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 读取预处理过的数据\n",
    "original_1 = pd.read_excel('./第三问top33.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 根据单品求损耗率\n",
    "def compute_rate(danpin):\n",
    "    \n",
    "    sunhao = original_1[original['单品编码'] == danpin]['损耗率']\n",
    "    return sunhao.iloc[0]*0.01"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 给单品和进货量-求出陈列量\n",
    "def compute_chenlie(danpin,jinhuo):\n",
    "    chenlie=jinhuo*(1-compute_rate(danpin))\n",
    "\n",
    "    return chenlie"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 给单品-求进价\n",
    "def compute_jinjia(danpin):\n",
    "    sunhao = original_1[original_1['单品编码'] == danpin]['平均进价']\n",
    "    return sunhao.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 给单品-求成本加成定价\n",
    "def compute_dingjia(danpin):\n",
    "    sunhao = original_1[original_1['单品编码'] == danpin]['修正后成本加成定价预测']\n",
    "    return sunhao.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5.223263"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "compute_dingjia(102900011030059)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 给出单品编码、进货量-求出该商品的利润\n",
    "def compute_lirun(danpin,jinhuo):\n",
    "    lirun=compute_chenlie(danpin,jinhuo)*compute_dingjia(danpin)-jinhuo*compute_jinjia(danpin)\n",
    "\n",
    "    return lirun"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "x = 0\n",
    "a = 0.97  # 退火速度\n",
    "T = 100  # 初始温度\n",
    "min_quanju = T  # 全局最优解初始化\n",
    "min_x = 0  # 全局最优解对应X坐标\n",
    "n = 0  # 退火计次\n",
    "listn = []\n",
    "list_min = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "while T > 1:  # 终止条件\n",
    "\n",
    "    min_jubu = T\n",
    "    t = T\n",
    "    min_x1 = random.randint(0, 500)/100\n",
    "\n",
    "    for i in range(2):\n",
    "        x1 = min_x1 + random.randint(-100, 100)/100\n",
    "        y1 = mbhs(x1)\n",
    "        dt = y1 - min_jubu\n",
    "        if dt < 0 or dt == 0:\n",
    "            t = y1\n",
    "            min_x1 = x1\n",
    "        else:\n",
    "            zhishu = -dt/t\n",
    "            met = math.exp(zhishu)\n",
    "            pd = random.randint(0, 1000000)/1000000\n",
    "            if pd <= met:\n",
    "                t = y1\n",
    "                min_x1 = x1\n",
    "        min_jubu = t\n",
    "\n",
    "    if min_jubu <= min_quanju:\n",
    "        min_quanju = min_jubu\n",
    "        min_x = min_x1\n",
    "\n",
    "    print('局部最优解：', min_jubu)\n",
    "    T = T * a\n",
    "\n",
    "    n += 1\n",
    "    listn.append(T)\n",
    "    list_min.append(min_jubu)\n",
    "\n",
    "print('全局最优解：', min_quanju)\n",
    "print('最优解时X取值:', min_x)\n",
    "\n",
    "# 制图\n",
    "X = list_x = []\n",
    "Y = list_y = []\n",
    "c = 0\n",
    "for i in range(500):\n",
    "    list_x.append(i/100)\n",
    "    list_y.append(mbhs(i/100))\n",
    "\n",
    "#图中使用汉字\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei']\n",
    "plt.rcParams['axes.unicode_minus'] = False\n",
    "\n",
    "#最优解绘图\n",
    "plt.subplot(121)\n",
    "plt.plot(X, Y)\n",
    "plt.scatter(min_x, min_quanju, c='r', label='全局最优解')\n",
    "plt.legend()\n",
    "\n",
    "#退火过程\n",
    "plt.subplot(122)\n",
    "plt.plot(listn, list_min)\n",
    "plt.gca().invert_xaxis()\n",
    "\n",
    "plt.show()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
